﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using MarketOnlineDTO;
using MySql.Data.MySqlClient;


namespace MarketOnlineDAO
{
    public class ThongKeDAO : BaseDataProvider
    {
        MySqlCommand _cmd;


        public List<ThongKeDTO> ThongKe(DateTime Ngay)
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select sp.TenSP,cthoadon.DonGia, cthoadon.SoLuong, dvt.TenDonViTinh, SUM(cthoadon.DonGia*cthoadon.SoLuong) as ThanhTien";
            _cmd.CommandText = _cmd.CommandText  + " from HoaDon hd, chitiethoadon cthoadon, sanpham sp, donvitinh dvt";
            _cmd.CommandText = _cmd.CommandText + " where hd.MaHD=cthoadon.MaHD and cthoadon.MaSP=sp.MaSP";
            _cmd.CommandText = _cmd.CommandText + " and day(hd.NgayGioDatHang)= '"+Ngay.Day+"'";
            _cmd.CommandText = _cmd.CommandText + " and month(hd.NgayGioDatHang)= '"+Ngay.Month+"'";
            _cmd.CommandText = _cmd.CommandText + " and year(hd.NgayGioDatHang)= '"+Ngay.Year+"'";
            _cmd.CommandText = _cmd.CommandText + " and hd.MaTinhTrangHD=2";
            _cmd.CommandText = _cmd.CommandText + " and sp.MaDonViTinh=dvt.MaDonViTinh";
            _cmd.CommandText = _cmd.CommandText + " Group by sp.TenSP, cthoadon.DonGia, cthoadon.SoLuong, dvt.TenDonViTinh";

           
            _cmd.CommandType = CommandType.Text;

            DataTable dt = new DataTable();

            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }

            List<ThongKeDTO> list = new List<ThongKeDTO>();

            foreach (DataRow row in dt.Rows)
            {
                ThongKeDTO dto = new ThongKeDTO();
                dto.TenSP= row["TenSP"].ToString();
                dto.SoLuong = Double.Parse(row["SoLuong"].ToString());
                dto.DonGia = Double.Parse(row["DonGia"].ToString());
                dto.TenDonViTinh = row["TenDonViTinh"].ToString();
                dto.ThanhTien = Double.Parse(row["ThanhTien"].ToString());

                list.Add(dto);
            }

            return list;
        }

    }
}
